IF OBJECT_ID('dbo.fnGetMultiCriteriaCodeSort') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetMultiCriteriaCodeSort
    IF OBJECT_ID('dbo.fnGetMultiCriteriaCodeSort') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetMultiCriteriaCodeSort >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetMultiCriteriaCodeSort >>>'
END
GO
-- =======================================================================
-- Function Name: dbo.fnGetMultiCriteriaCodeSort()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/22/2008
--
-- Description: Parses long string to table result of shorter strings
--		like fnGetMultiCriteriaCode, but also adds SortOrder column
--             
--
-- Parameters:  @array varchar(2000)
--              
-- Returns:     table 	(SortOrder, INT,
--					Code VARCHAR(100))
--
-- SampleCall:
-- SELECT @PersonName = fnGetMultiCriteriaCodeSort ( 1234 )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/22/08    KC        Created
--
-- =======================================================================
CREATE  FUNCTION dbo.fnGetMultiCriteriaCodeSort (@array varchar(2000))
RETURNS  @Criteria table 
        (SortOrder		INT IDENTITY,
        Code 			VARCHAR(100) PRIMARY KEY)
AS
BEGIN

     DECLARE @separator char(1)
     SET @separator = ','

     DECLARE @separatorposition int 
     DECLARE @arrayvalue varchar(1000) 
     
     SET @array = @array + ','
     
     WHILE patindex('%,%' , @array) <> 0 
     BEGIN
     
		SELECT @separatorposition =  patindex('%,%' , @array)
		SELECT @arrayvalue = left(@array, @separatorposition - 1)

		INSERT @Criteria (Code)
		VALUES (@arrayvalue)

       SELECT @array = stuff(@array, 1, @separatorposition, '')
     END

     RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fnGetMultiCriteriaCodeSort') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetMultiCriteriaCodeSort >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetMultiCriteriaCodeSort >>>'
go
GRANT SELECT ON dbo.fnGetMultiCriteriaCodeSort TO InsightReport,Insight
go

